﻿using System;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic; 
using System.Data;
using TimeTravel.Components;
using TimeTravel.Extentions;
using TimeTravel.IData;

namespace TimeTravel.SqlServer  
{
    public class AnnouncementData : IAnnouncementData
    {
        #region Fields
        private static readonly SqlHelper DbHelper = new SqlHelper();
        private static readonly string[] V_Columns = {
                                   "[id]","[title]","[body]", "[sort]","[click]","[date]","[userid]","[username]","[nickname]","[userpicture]","[lastlogin]","[useremail]"
                               }; 
        #endregion

        #region Implementation of IDataBase<NavMenuInfo>

        /// <summary>
        /// 添加记录
        /// </summary>
        public int Add(AnnouncementInfo announcement)
        {
            if (announcement == null) return 0;
            var strSql = new StringBuilder();
            strSql.Append("insert into Announcement(");
            strSql.Append("[title],[body],[sort],[click],[date],[userid])");
            strSql.Append(" values ");
            strSql.Append("(@title,@body,@sort,@click,@date,@userid);");
            strSql.Append("select @@IDENTITY ");
            SqlParameter[] parameters = {
                                            new SqlParameter("@title", SqlDbType.NVarChar, 100),
                                            new SqlParameter("@body", SqlDbType.Text, 16),
                                            new SqlParameter("@sort", SqlDbType.Int),
                                            new SqlParameter("@click", SqlDbType.Int),
                                            new SqlParameter("@date", SqlDbType.Int),
                                            new SqlParameter("@userid", SqlDbType.Int)
                                        };
            parameters[0].Value = announcement.Title;
            parameters[1].Value = announcement.Body;
            parameters[2].Value = announcement.Sort;
            parameters[3].Value = announcement.Click;
            parameters[4].Value = announcement.Date;
            parameters[5].Value = announcement.User.ID;

            var result =  DbHelper.ExecuteScalar(CommandType.Text, strSql.ToString(), parameters);
            return result == null ? 0 : Convert.ToInt32(result);
        }

        /// <summary>
        /// 删除记录（逻辑删除）
        /// </summary>
        /// <param name="id"></param>
        public void Delete(int id)
        {
            if (id == 0) return;
            var strSql = new StringBuilder();
            strSql.Append("update Announcement set DeleteFlag = 1 where [id]=@id;");
            SqlParameter[] parameters = {
                                            new SqlParameter("@id", SqlDbType.Int)
                                        };
            parameters[0].Value = id;
            DbHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(),parameters);
        }

        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="announcement"></param>
        public void Update(AnnouncementInfo announcement)
        {
            if (announcement == null || announcement.ID == 0) return;
            var strSql = new StringBuilder();
            strSql.Append("update Announcement set ");
            strSql.Append("[title] = @title,");
            strSql.Append("[body] = @body,");
            strSql.Append("[sort] = @sort,");
            strSql.Append("[date] = @date ");
            strSql.Append("where [id] = @id;");
            SqlParameter[] parameters = {
                                            new SqlParameter("@title", SqlDbType.NVarChar, 100),
                                            new SqlParameter("@body", SqlDbType.Text, 16),
                                            new SqlParameter("@sort", SqlDbType.Int),
                                            new SqlParameter("@date", SqlDbType.Int),
                                            new SqlParameter("@id",SqlDbType.Int)
                                        };
            parameters[0].Value = announcement.Title;
            parameters[1].Value = announcement.Body;
            parameters[2].Value = announcement.Sort;
            parameters[3].Value = announcement.Date;
            parameters[4].Value = announcement.ID;
            DbHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
        }

        /// <summary>
        /// 获取所有
        /// </summary>
        /// <returns></returns>
        public IList<AnnouncementInfo> GetAll()
        {
            var strSql = new StringBuilder();
            strSql.AppendFormat("select {0} from V_Announcement ", String.Join(",", V_Columns));
            strSql.Append(" where DeleteFlag = 0 ;");
            var result = DbHelper.ExecuteObjectList(strSql.ToString(), DataReaderParser);
            return result.ToList();
        }

        /// <summary>
        /// 根据条件获取
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public RecordSet<AnnouncementInfo> GetBySearch(PageQueryInfo query)
        {
            if(query == null) return new RecordSet<AnnouncementInfo>();
            return DbHelper.ExecutePaging("V_Announcement", query, DataReaderParser, V_Columns);
        }

        /// <summary>
        /// 获取详细信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public AnnouncementInfo GetOne(int id)
        {
            if(id == 0) return null;
            var strSql = new StringBuilder();
            strSql.AppendFormat("select {0} from V_Announcement ", String.Join(",", V_Columns));
            strSql.Append("where [id] = @id;");
            SqlParameter[] parameters = {
                                            new SqlParameter("@id", SqlDbType.Int)
                                        };
            parameters[0].Value = id;
            return DbHelper.ExecuteObject(CommandType.Text, strSql.ToString(), DataReaderParser,parameters);
        }

        /// <summary>
        /// 获取总数
        /// </summary>
        /// <returns></returns>
        public int GetTotalCount()
        {
            var strSql = new StringBuilder();
            strSql.Append("select count(id) from Announcement ");
            strSql.Append("where DeleteFlag = 0;");
            var result = DbHelper.ExecuteScalar(CommandType.Text, strSql.ToString());
            return result == null ? 0 : Convert.ToInt32(result);
        }

        /// <summary>
        /// 更新公告查看次数
        /// </summary>
        /// <param name="id"></param>
        public void UpdateClickCount(int id)
        {
            if (id == 0) return;
            var strSql = new StringBuilder();
            strSql.Append("update Announcement set ");
            strSql.Append("click = click +1 ");
            strSql.Append("where [id] = @id;");
            SqlParameter[] parameters = {
                                            new SqlParameter("@id", SqlDbType.Int)
                                        };
            parameters[0].Value = id;
            DbHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
        }

        #endregion

        #region BuildNavMenuObject

        /// <summary>
        /// 从DataReader中构建对象
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private AnnouncementInfo DataReaderParser(IDataReader reader)
        {
            var announcement = new AnnouncementInfo
            {
                ID = reader.GetInt32("id"),
                Sort = reader.GetInt32("sort"),
                Title = reader.GetString("title"),
                Body = reader.GetString("body"),
                Click = reader.GetInt32("click"),
                Date = reader.GetInt32("date")
            };
            var sysUser = new SysUserInfo
            {
                ID = reader.GetInt32("userid"),
                Name = reader.GetString("username"),
                NickName = reader.GetString("nickname"),
                Picture = reader.GetString("userpicture"),
                Email = reader.GetString("useremail")
            };
            announcement.User = sysUser;
            return announcement;
        }

        #endregion
    }
}

